/*
drop table datehasdayofweekrelationship;
drop table datehasshiftrelationship;
drop table shiftinsidedepartmentrelationship;
drop table shiftneedsskillrelationship;
drop table shifthasdayofweekrelationship;
drop table personhaspreferredshiftrelationship;
drop table personhasassaignedshiftrelationship;
drop table departmenthaslocationrelationship;
drop table departmentneedskillrelationship
drop table paygradeneedsskillrelationship;
drop table titlehaspermissionrelationship;
drop table D;
drop table DayOfWeek;
drop table Department;
drop table Location;
drop table Paygrade;
drop table Permission;
drop table Shift;
drop table Skill;
drop table Title;
drop table departmentmanagedbypersonrelationship;
drop table personcalloffdaterelationship;
drop table personhasdepartmentrelationship;
drop table personhaspaygraderelationship;
drop table personhasskillrelationship;
drop table personhassuperiorrelationship;
drop table personhastitlerelationship;
drop table personpreferrdepartmentrelationship;
drop table personvacationdaterelationship;
drop table Person;
*/


/*
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Doe', '40','username','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Squat', '40','username2','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','1');
insert into d ( d ) values (getDate());  
insert into dayofweek ( letter  ,  name ) values ('M','Monday');
insert into department ( name ) values ('Public Relations');  
insert into department ( name ) values ('Operating Ward');  
insert into location ( name ) values ('East Hall');  
insert into paygrade ( name  ,  num ) values ('Minimum Wage','7.50');
insert into permission ( name ) values ('View Schedule');  
insert into Permission  (name) values ('Edit Schedule');
insert into department ( name ) values ('Emergency Room');  
SELECT COUNT(deid) AS numrows FROM Department
insert into shift ( name ,  starthour ,  endhour ) values('Morning', '0600', '1200');
insert into shift ( name ,  starthour ,  endhour ) values('Afternoon', '1200', '1800');
insert into shift ( name ,  starthour ,  endhour ) values('Night', '1800', '0600');
insert into skill ( name ) values ('Draw Blood');  
insert into title ( name  ,  acronym ) values ('Nurse Assistant','NurAs');
insert into title ( name  ,  acronym ) values ('Nurse','Nur');
insert into Location(name) values ('Main Lobby');
insert into Title ( name  ,  acronym ) values ('Manager','Man');
--------------------------------------------------------------------------
insert into datehasdayofweekrelationship(did, dowid) values (1,1);
insert into datehasshiftrelationship(did, shid) values (1,1);
insert into datehasshiftrelationship(did, shid) values (1,2);
insert into datehasshiftrelationship(did, shid) values (1,3);
insert into departmenthaslocationrelationship(deid, lid) values (1,1);
insert into personcalloffdaterelationship(perid,did) values (1,1);
insert into personvacationdaterelationship(perid, did) values (4,1);
insert into personhasdepartmentrelationship(perid, deid) values(1,1);
insert into personhasdepartmentrelationship(perid, deid) values(4,3);
insert into personpreferrdepartmentrelationship(perid, deid) values(1,1);
insert into personpreferrdepartmentrelationship(perid, deid) values(4,3);
insert into departmentmanagedbypersonrelationship(deid, perid) values (3,4);
insert into departmentmanagedbypersonrelationship(deid, perid) values (1,1);
insert into personhassuperiorrelationship(peridA, peridB) values (4,1);
insert into paygrade (name,num) values ('MediumWage','8.10');
insert into personhaspaygraderelationship(perid,paid)values (1,1);
insert into personhaspaygraderelationship(perid,paid)values (4,5);
insert into personhastitlerelationship(perid,tid)values (1,1);
insert into personhastitlerelationship(perid,tid)values (4,2);
insert into titlehaspermissionrelationship(tid, peid)values (1,1);
insert into titlehaspermissionrelationship(tid, peid)values (2,1);
insert into paygradeneedsskillrelationship(paid,sid) values(1,1);
insert into paygradeneedsskillrelationship(paid,sid) values(5,1);
insert into personhasskillrelationship(perid,sid) values (1,1);
insert into personhasskillrelationship(perid,sid) values (4,1);
insert into departmentneedskillrelationship(deid,sid) values (3,1);
insert into departmenthaslocationrelationship(deid, lid) values (1,2);
insert into departmenthaslocationrelationship(deid, lid) values (3,1);
INSERT INTO DayOfWeek (letter, name) VALUES     ('T', 'Tuesday');
INSERT INTO DayOfWeek (letter, name) VALUES     ('W', 'Wednesday');
INSERT INTO DayOfWeek (letter, name) VALUES     ('R', 'Thursday');
INSERT INTO DayOfWeek (letter, name) VALUES     ('F', 'Friday');
INSERT INTO DayOfWeek (letter, name) VALUES     ('S', 'Saturday');
INSERT INTO DayOfWeek (letter, name) VALUES     ('S', 'Sunday');
INSERT INTO Paygrade (name, num) values ('large wage','20');
---------------------------------------------------
SELECT * from Person;
SELECT * from D;
Select * from Paygrade;
select * from Permission;
select * from Title;
select * from datehasdayofweekrelationship;
select * from D;
select * from Skill;
select* from Skill;
select * from Location;
Select * from Department;
select * from paygradeneedsskillrelationship;
select * from Department WHERE name = '';   --returns nothing, there is no department with no name
Select * from Person;
SELECT P.perid FROM Person P WHERE P.username = '14c4b06b824ec593239362517f538b29' AND P.password = '5f4dcc3b5aa765d61d8327deb882cf99';
select * from Paygrade;
SELECT * FROM PayGrade WHERE paid = '1';
SELECT paid FROM Paygrade WHERE name = 'Minimum Wage' AND num = '7.5';
Select * from personcalloffdaterelationship;
SELECT R.did from personcalloffdaterelationship R where R.did = '1';
SELECT deid FROM personpreferrdepartmentrelationship R WHERE R.perid ='1';
SELECT perid FROM departmentmanagedbypersonrelationship WHERE deid='1'
insert into personhastitlerelationship(perid,tid)values (1,3);
select * from personhasskillrelationship;
select * from personhastitlerelationship;
select * from Title;
insert into title ( name  ,  acronym ) values ('Manager','Man');
insert into personhastitlerelationship(perid, tid) values (1,3);

alter table Person add address varchar(128) not null default '123 fake st';

update Person set address='435 fake st' where perid=4;
select * from Shift;
DELETE FROM Shift WHERE shid='1'
insert into Shift(name,starttime,endtime) values ('temp',getDate(),GETDATE());
SELECT shid FROM Shift WHERE starttime>='NOV 01 2010' AND endtime<='NOV 20 2010';
UPDATE Shift SET name='temp2', starttime=getdate(), endtime=getdate() WHERE shid='2'
select * from shifthasdayofweekrelationship;
select * from dayofweek
INSERT INTO shifthasdayofweekrelationship (shid, dowid) values ('2','1')
select * from Person;
select * from personhasassaignedshiftrelationship
INSERT INTO personhasassaignedshiftrelationship (perid, shid) VALUES ('3','2')
select * from skill
select * from shiftneedsskillrelationship
INSERT INTO shiftneedsskillrelationship (shid, sid) VALUES ('2','2')

*/

/*
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Smith', '40','username3','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Squat', '40','username5','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Doe', '40','username6','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Smith', '40','username7','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Doe', '40','username8','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Squat', '40','username9','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Smith', '40','username10','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Smith', '40','username11','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Doe', '40','username12','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Squat', '40','username13','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Doe', '40','username14','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','1');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Squat', '40','username15','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Doe', '40','username16','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Smith', '40','username17','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Doe', '40','username18','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Squat', '40','username19','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Doe', '40','username20','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Squat', '40','username21','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Doe', '40','username22','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Smith', '40','username23','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Doe', '40','username24','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Squat', '40','username25','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Doe', '40','username26','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Squat', '40','username27','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Doe', '40','username28','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Smith', '40','username29','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Doe', '40','username31','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Squat', '40','username31','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Doe', '40','username32','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Smith', '40','username33','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Doe', '40','username34','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Squat', '40','username35','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Smith', '40','username36','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Squat', '40','username37','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Doe', '40','username38','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Squat', '40','username39','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Smith', '40','username40','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Squat', '40','username41','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('John', 'Q','Doe', '40','username42','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Squat', '40','username43','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Smith', '40','username44','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Squat', '40','username45','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Doe', '40','username46','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Jack', 'Q','Squat', '40','username47','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Smith', '40','username48','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('David', 'Q','Squat', '40','username49','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Eric', 'Q','Doe', '40','username50','5f4dcc3b5aa765d61d8327deb882cf99','jr','555-555-5555','666-666-6666','123 fake street','1','0');
insert into Person(firstname, middlename, sirname, approxHours, username, password, suffix, homephone, cellphone, address,active, manager) values ('Peter', 'Q','Squat', '40','username4','6cb75f652a9b52798eb6cf2201057c73','jr','555-555-5555','666-666-6666','124 fake street','1','0');
*/